Hands On: Modifying a Report 

Often, you will want to create reports that are more complex than the simple report you created in the previous exercise. Summarizing data, incorporating data from more than one database, or providing multiple views of the same data are typical requirements for many, more complex reports. Seagate Crystal Reports has several powerful tools that enable you to design any report you can imagine.

In this exercise, you'll start with an existing simple report and use Seagate Crystal Reports to make several modifications to the report.

Before you begin

Ensure that you have completed the previous exercise and know the name and location of the sample report you created. This report will be used as the starting point for this exercise. 

Step by step 

1.  Open Seagate Crystal Reports 8.

2.  Open the report you created in the previous exercise by selecting Open... from the File menu and browsing to find your file.

Creating a Formula

In many cases, the data you want to show in your report already exists in fields within database tables. Sometimes, however, you need to put information in your report that does not exist in any of the data fields. In such cases, you must create a formula. For example, to calculate each customer’s sales for last year as a percentage of total sales for the region, you need to know the subtotal of sales for the region as well as the sales for each individual customer in that region.

3.  Select the Design tab in the report window at the top left corner of the report.

4.  From the Insert menu, select Formula Field...

5.   Click the New...  button. The Formula Name dialog box appears.

6.   Name the formula "% of Sales" and click OK. The Formula Editor window appears. The Formula Editor lets you create, test, and modify your formula.  

7.  Enter the following formula in the Formula Editor window by double-clicking each field in the Formula Editor lists:

{Customer.Last Year's Sales} % Sum ({Customer.Last Year's Sales}, {Customer.Region})

  • Double-click "Customer.Last Year's Sales" from the Report Fields list (Left pane)
  • Double-click "Per cent [ x%y ]" from the Arithmetic group in the Operators list (Right pane)
  • Double-click "Group #1:Sum of Last Year's Sales" from the Fields list (Left pane)

The operator you selected automatically formats the result of the formula to print as a percentage.

When you select items from the lists, they are automatically entered in the formula window complete with brackets, punctuation, and other syntax items. If you enter your formula manually by typing them in, make certain you enter those syntax items yourself. It is safer and faster to build a formula by choosing list box items.

Scroll through the Functions list to see the wide range available. Functions are built-in procedures or subroutines used to evaluate, calculate, or transform data. They make it easy for you to create formulas without coding. The Formula Editor includes financial functions that let you place arrays and ranges in formulas. The formula language has been expanded to include case statements, looping and dynamic arrays. You also have a choice of selecting either Crystal or Basic-like syntax in formulas. You can also extend functions with your own custom functions by creating them with any COM-compliant language such as Visual Basic, Visual C++ and Delphi.

8.  Click the  save and close button at the top of the Formula Editor to return to the Field Explorer.

9.  Click the Insert to Report button on the top left to place the field in your report. When you move the pointer, it changes to a gray field object box attached to the cursor.

10.  Position the field object box in the Details section of your report, to the right of the Last Year's Sales column. Click to insert the field.

11.  Click Close in the Field Explorer dialog box. You have now created a new field in your report that uses a simple formula to calculate the percentage contribution of each customer to the total sales for the region. You may need to move the surrounding fields to make room for this new field. You can also right-click the field and select Format Field to choose a number style. Seagate Crystal Reports has over 160 built-in formula functions that enable you to perform a wide range of calculations on data in your database.

Highlighting Important Data

To manipulate the formatting in different sections of your report, you can use formulas created in the Formula Editor to control field and object attributes such as color, font, border, underline, and strikeout. Using formulas, you can conditionally format data based on whatever criteria you select. With Seagate Crystal Reports, you can also use the Highlighting Expert to identify important data in your report.

In the following example we'll use highlighting to display percentages of last year’s sales in red if they are less than 20% and in blue if they are greater than 30%. 

12.   Select the Preview tab in the report window. A preview of your report appears.

13.   Position the mouse pointer over any number in the "% of Sales" column and click to select it. A rectangle appears around that entry and the rest of the column becomes shaded to indicate that all similar fields in this column have also been selected. 

14.  Click the right mouse button. The shortcut menu for that column appears.

15.   Select Highlighting Expert... from the shortcut menu. The Highlighting Expert window appears.

16.    In the Item List window, click new item to create a new rule.

17.    Select "less than" from the Value is list and type the number 20 in the value box. In the Font Color list, select "Red.” This completes the first condition we wanted to highlight.

18.    Click new item in the Item List window again to create the next rule. Select "greater than" from the Value is: list and type the number 30 in the value box. In the Font Color list, select "Blue." This completes the second condition.

19.   Click OK to save the new highlighting and return to the Preview window. You'll notice that all sales percentages less than 20% or greater than 30% are now highlighted in the appropriate color. Any other value is displayed in the default color. Highlighting is a powerful way to spot important values, especially in complex or detailed reports.

Inserting a Map

Sometimes, there are important relationships in your report that depend on geographic location. These dependencies are often difficult to uncover using a report with conventional tables and charts. With the Geographic Mapping feature in Seagate Crystal Reports, you can add a visual view of your data, superimposed on an appropriate map. In this exercise, we'll add a map to the simple sales report and alter the appearance of the map.

20.  Before adding the map, locate the pie chart at the beginning of the report, right-click it and select Delete.

21.  From the Report menu, select Top N/Sort Group Expert... Under For this group sort, select All and click OK.

22.  From the Insert menu, select Map... The Map Expert window appears. The default values shown in the Data window maps the Sum of Last Year’s Sales by Region. Accept these defaults.

23.    Click the Type tab at the top of the window. You can change the type of map produced as well as set the colors used to display data. Accept the rest of the default values, but we will change to colors used to display the data.

24.  From the "Color of highest interval" list, select the color in the top row labeled "Dark green". In the "Color of lowest interval" list, select the color in the bottom row labeled "Pale green"

25.     Click OK to continue. Seagate Crystal Reports analyzes the "State/County" field and determines that it contains data for all of the states in the United States. It then retrieves the appropriate map and shades each state from pale green to dark green based on the magnitude of sales. Like charts, maps support drill-down in Preview mode. If you double-click one of the states in the map, you are presented with underlying sales detail for that state.

26.  Right-click the map and select Map Analyzer... from the menu. This opens a third tabbed window, which presents the Analyzer view of the report. In the Analyzer, you can right-click to zoom in, zoom out and pan the map to zero in a particular region. Try zooming in on Southern California to see additional detail such as cities and main roads. You’ll notice a Map Lens, new in Version 8, on the bottom right of your screen.

This Map Lens shows the portion of the map relative to the full map displayed in the Analyzer. Any changes you make in the Analyzer view are retained in the Preview window, letting you interactively customize the map view presented without recreating the map itself.

Advanced Modifications

There are many other advanced modifications you can make to reports. This includes the use of cross-tabs or subreports to examine different views of the same data. Reports can be created which use built-in SQL query features to perform report processing on the database server.